package com.soso.taste.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.soso.taste.pojo.Amass;
import com.soso.taste.pojo.Taste;
import com.soso.taste.util.DB;

public class TasteDaoImpl implements TasteDao {
	public int getSum(String tablename) {
		int sum = 0;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = DB.getConnection();
			stmt=conn.createStatement();
			String sql = null;
			sql="select count(*) from "+tablename;
			rs=stmt.executeQuery(sql);
			rs.next();
			sum = rs.getInt(1);
		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn,stmt,rs);
		}
		return sum;
	}

	public ArrayList<Object> getLists(String tablename, int nowpage, int max) {
		ArrayList<Object> objects = new ArrayList<Object>();
		int start = max*(nowpage-1);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		//System.out.println(page);
		try {
			conn = DB.getConnection();
			stmt=conn.createStatement();
			String sql = null;
			//System.out.println(page==1);
			if(nowpage==1)sql="select * from "+tablename+" ORDER BY id DESC LIMIT "+max;
			else sql="select * from "+tablename+" ORDER BY id DESC LIMIT "+start+","+max;
			//System.out.println(sql);
			rs=stmt.executeQuery(sql);
			while(rs.next()){
				if(tablename.equals("taste")){
					Taste taste = new Taste();
					taste.setId(rs.getInt("id"));
					taste.setCall(rs.getString("call"));
					taste.setOpenid(rs.getString("openid"));
					taste.setPart(rs.getString("part"));
					taste.setTitle(rs.getString("title"));
					taste.setHref(rs.getString("href"));
					objects.add(taste);
				}else if (tablename.equals("amass")) {
					Amass amass = new Amass();
					amass.setId(rs.getInt("id"));
					amass.setOpenid(rs.getString("openid"));
					amass.setWord(rs.getString("word"));
					amass.setTitle(rs.getString("title"));
					amass.setHref(rs.getString("href"));
					objects.add(amass);
				}else{}
			}
		}catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}finally {
			close(conn,stmt,rs);
		}
		return objects;
	}

	public int Save(List<Amass> amasses) {
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			conn = DB.getConnection();
			pstmt = conn.prepareStatement("insert into amass values (null, ?, ?, ?, ?)");
			for(int i=0;i<amasses.size();i++){
				Amass amass = amasses.get(i);
				pstmt.setString(1, amass.getOpenid());
				pstmt.setString(2, amass.getWord());
				pstmt.setString(3, amass.getTitle());
				pstmt.setString(4, amass.getHref());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(conn, pstmt);
		}
		return 0;
	}

	public int IsExist(String openid, String href) {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		try {
			conn = DB.getConnection();
			stmt = conn.createStatement();
			String sql = "select count(*) from taste where openid='"+openid+"' and href='"+href+"'";
			//System.out.println(sql);
			rs = stmt.executeQuery(sql);
			rs.next();
			return rs.getInt(1);
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(conn, stmt, rs);
		}
		return 0;
	}

	public int Save(Taste taste) {
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			conn = DB.getConnection();
			pstmt = conn.prepareStatement("insert into taste values (null, ?, ?, ?, ?, ?)");
			pstmt.setString(1, taste.getCall());
			pstmt.setString(2, taste.getOpenid());
			pstmt.setString(3, taste.getPart());
			pstmt.setString(4, taste.getTitle());
			pstmt.setString(5, taste.getHref());
			pstmt.executeUpdate();
			return 1;
		}catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(conn, pstmt);
		}
		return 0;
	}
	
	public void close(Connection conn,Statement stmt,ResultSet rs){
		try {
			if(rs != null) {
				rs.close();
				rs = null;
			}
			if(stmt != null) {
				stmt.close();
				stmt = null;
			}
			if(conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void close(Connection conn,PreparedStatement pstmt){
		try {
			if(pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
			if(conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
